<?php
class media_model extends CI_Model {
	public function __construct() {
		parent::__construct ();
		$this->db = $this->load->database ( 'default', true );
	}
	public function query_featured_list($media_type, $limit_num) {
		$media_type = $this->db->escape_str ( $media_type );
		$sql = "select 	media.media_id,
						location,
						reference_price,
						pic,
						media_type_name
				from website_featured_media inner join media 
				on media.media_id =website_featured_media.media_id
				inner join media_pic on media.media_id =media_pic.media_id
				inner join sys_media_type on sys_media_type.media_type_id =media.media_type_id
				
				where media.media_type ='$media_type' 
				group by media.media_id 
				order by sort_num desc,pic_id asc
				limit $limit_num ";
		$query = $this->db->query ( $sql );
		if ($query->num_rows () > 0) {
			return $query->result_array ();
		}
	}
	public function query_media_type_list() {
		$sql = "select media_type_id,media_type_name from sys_media_type where be_delete ='0' order by media_type_id desc";
		$query = $this->db->query ( $sql );
		if ($query->num_rows () > 0) {
			return $query->result_array ();
		}
	}
	public function search_media_num($seach) {
		$sql = "select count(media.media_id) as num
				from  media 
				";
		// 判断是否加入了点位属性
		if ($seach ['env_type'] != "") {
			$env_type = $this->db->escape_str ( $seach ['env_type'] );
			$sql .= "inner join media_env  on media.media_id =media_env.media_id 
					where 1 =1 
					and env_type ='$env_type' ";
		} else {
			$sql .= "where 1 =1 ";
		}
		
		// 判断是否筛选了媒体类型
		if ($seach ['media_type'] != "") {
			$media_type = $this->db->escape_str ( $seach ['media_type'] );
			$sql .= "and  media_type ='$media_type' ";
		}
		// 判断是否筛选了省份
		if ($seach ['province'] != "") {
			$province = $this->db->escape_str ( $seach ['province'] );
			$sql .= "and  province ='$province' ";
		}
		// 判断是否筛选了城市
		if ($seach ['city'] != "") {
			$city = $this->db->escape_str ( $seach ['city'] );
			$sql .= "and  city ='$city' ";
		}
		// 判断是否筛选了区域
		if ($seach ['area'] != "") {
			$area = $this->db->escape_str ( $seach ['area'] );
			$sql .= "and  region ='$area' ";
		}
		// 判断是否筛选了照明情况
		if ($seach ['illumination'] !== "") {
			$illumination = $this->db->escape_str ( $seach ['illumination'] );
			$sql .= "and  illumination ='$illumination' ";
		}
		// 判断是否筛选了媒体形式
		if ($seach ['media_type_id'] != "") {
			$media_type_id = $this->db->escape_str ( $seach ['media_type_id'] );
			$sql .= "and  media.media_type_id ='$media_type_id' ";
		}
		// 判断是否筛选了价格
		if ($seach ['price'] != 0) {
			$price = explode ( "-", $seach ['price'] );
			$start_price = $this->db->escape_str ( $price [0] );
			$end_price = $this->db->escape_str ( $price [1] );
			$sql .= "and  reference_price >='$start_price' and reference_price<='$end_price' ";
		} else {
			if ($seach ['start_price'] != "") {
				$start_price = $this->db->escape_str ( $seach ['start_price'] );
				$sql .= "and  reference_price >='$start_price' ";
			}
			if ($seach ['end_price'] != "") {
				$end_price = $this->db->escape_str ( $seach ['end_price'] );
				$sql .= "and  reference_price <='$end_price' ";
			}
		}
		$query = $this->db->query ( $sql );
		if ($query->num_rows () > 0) {
			$row = $query->row_array ();
			return $row ['num'];
		}
	}
	public function search_media($seach, $start, $limit) {
		$sql = "select media.media_id,
						location,
						reference_price,
						pic,
						specification,
						media_type_name
				from  media 
				inner join media_pic on media.media_id =media_pic.media_id
				inner join sys_media_type on sys_media_type.media_type_id =media.media_type_id
				";
		// 判断是否加入了点位属性
		if ($seach ['env_type'] != "") {
			$env_type = $this->db->escape_str ( $seach ['env_type'] );
			$sql .= "inner join media_env  on media.media_id =media_env.media_id 
					where 1 =1 
					and env_type ='$env_type' ";
		} else {
			$sql .= "where 1 =1 ";
		}
		
		// 判断是否筛选了媒体类型
		if ($seach ['media_type'] != "") {
			$media_type = $this->db->escape_str ( $seach ['media_type'] );
			$sql .= "and  media_type ='$media_type' ";
		}
		// 判断是否筛选了省份
		if ($seach ['province'] != "") {
			$province = $this->db->escape_str ( $seach ['province'] );
			$sql .= "and  province ='$province' ";
		}
		// 判断是否筛选了城市
		if ($seach ['city'] != "") {
			$city = $this->db->escape_str ( $seach ['city'] );
			$sql .= "and  city ='$city' ";
		}
		// 判断是否筛选了区域
		if ($seach ['area'] != "") {
			$area = $this->db->escape_str ( $seach ['area'] );
			$sql .= "and  region ='$area' ";
		}
		if ($seach ['highway'] != "") {
			$highway = $this->db->escape_str ( $seach ['highway'] );
			$sql .= "and  highway_id ='$highway' ";
		}
		if ($seach ['airport'] != "") {
			$airport = $this->db->escape_str ( $seach ['airport'] );
			$sql .= "and  airport_id ='$airport' ";
		}
		
		if ($seach ['area'] != "") {
			$area = $this->db->escape_str ( $seach ['area'] );
			$sql .= "and  region ='$area' ";
		}
		
		// 判断是否筛选了照明情况
		if ($seach ['illumination'] !== "") {
			$illumination = $this->db->escape_str ( $seach ['illumination'] );
			$sql .= "and  illumination ='$illumination' ";
		}
		// 判断是否筛选了媒体形式
		if ($seach ['media_type_id'] != "") {
			$media_type_id = $this->db->escape_str ( $seach ['media_type_id'] );
			$sql .= "and  media.media_type_id ='$media_type_id' ";
		}
		// 判断是否筛选了价格
		if ($seach ['price'] != 0) {
			$price = explode ( "-", $seach ['price'] );
			$start_price = $this->db->escape_str ( $price [0] );
			$end_price = $this->db->escape_str ( $price [1] );
			$sql .= "and  reference_price >='$start_price' and reference_price<='$end_price' ";
		} else {
			if ($seach ['start_price'] != "") {
				$start_price = $this->db->escape_str ( $seach ['start_price'] );
				$sql .= "and  reference_price >='$start_price' ";
			}
			if ($seach ['end_price'] != "") {
				$end_price = $this->db->escape_str ( $seach ['end_price'] );
				$sql .= "and  reference_price <='$end_price' ";
			}
		}
		
		$sql .= "group by media_id order by " . $seach ['sort_by'] . " " . $seach ['sort_method'] . " limit $start,$limit";
		$query = $this->db->query ( $sql );
		if ($query->num_rows () > 0) {
			return $query->result_array ();
		}
	}
	public function query_media_type_name($media_type_id) {
		$media_type_id = $this->db->escape_str ( $media_type_id );
		$sql = "select media_type_name from sys_media_type where media_type_id ='$media_type_id' limit 1";
		$query = $this->db->query ( $sql );
		if ($query->num_rows () > 0) {
			$row = $query->row_array ();
			return $row ['media_type_name'];
		}
	}
	public function query_contrast_media_list($contrast_media_id_list) {
		if ($contrast_media_id_list == null) {
			return array ();
		}
		$id_list = "";
		foreach ( $contrast_media_id_list as $id ) {
			$id_list .= "'" . $id . "',";
		}
		$id_list = "(" . substr ( $id_list, 0, - 1 ) . ")";
		$sql = "select media.media_id,
						location,
						pic
				from  media
				inner join media_pic on media.media_id =media_pic.media_id
				where media.media_id in $id_list group by media.media_id ";
		$query = $this->db->query ( $sql );
		if ($query->num_rows () > 0) {
			return $query->result_array ();
		}
	}
	public function query_contrast_media_detail_list($contrast_media_id_list) {
		if ($contrast_media_id_list == null) {
			return array ();
		}
		$id_list = "";
		foreach ( $contrast_media_id_list as $id ) {
			$id_list .= "'" . $id . "',";
		}
		$id_list = "(" . substr ( $id_list, 0, - 1 ) . ")";
		$sql = "select media.media_id,
						location,
						pic,
						media_type,
						media_type_name,
						specification,
						specification_width,
						specification_height,
						crafts_name,
						illumination,
						reference_price,
						provice.region_name as province_name,
						city.region_name as city_name,
						area.region_name as area_name,
						corner_case_name
				from  media
				inner join media_pic on media.media_id =media_pic.media_id
				left join sys_media_type on sys_media_type.media_type_id =media.media_type_id
				inner join sys_region as provice on media.province =provice.region_id
				left join sys_region as city on media.city =city.region_id
				left join sys_region as area on media.region =area.region_id 
				left join sys_crafts on sys_crafts.crafts_id =media.crafts
				left join sys_corner_case on sys_corner_case.corner_case_id =media.corner_case 
				where media.media_id in $id_list group by media.media_id ";
		$query = $this->db->query ( $sql );
		if ($query->num_rows () > 0) {
			return $query->result_array ();
		}
	}
	public function query_statistics() {
		$sql = "select * from website_statistics";
		$query = $this->db->query ( $sql );
		if ($query->num_rows () > 0) {
			return $query->row_array ();
		}
	}
	public function query_media_announcement() {
		$sql = "select * from website_info_list where parent_id ='1' order by sort_num desc limit 5";
		$query = $this->db->query ( $sql );
		if ($query->num_rows () > 0) {
			return $query->result_array ();
		}
	}
	public function query_media_detail($media_id) {
		$media_id = $this->db->escape_str ( $media_id );
		$sql = "select
					m.*,
					r1.region_name as province_name,
					r2.region_name as city_name,
					r3.region_name as region_name,
					r3.region_type,
					sc.crafts_name,
					scc.corner_case_name,
					smt.media_type_name,
					sma.media_area_name,
					vv.company_name
				from
					media as m
					inner join sys_region as r1 on m.province =r1.region_id
					left join sys_region as r2 on m.city =r2.region_id
					left join sys_region as r3 on m.region =r3.region_id
					left join ven_vendor as vv on m.vendor =vv.vendor_id
					left join sys_crafts as sc on m.crafts =sc.crafts_id
					left join sys_corner_case  as scc on m.corner_case =scc.corner_case_id
					left join sys_media_type as smt on m.media_type_id =smt.media_type_id
					left join sys_media_area as sma on m.media_area =sma.media_area_id
				where
					m.media_id ='$media_id'
					limit 1";
		
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->row_array ();
		}
	}
	public function query_media_pic_list($media_id) {
		$media_id = $this->db->escape_str ( $media_id );
		$sql = "select pic from media_pic where media_id ='$media_id' order by pic_id asc";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function query_media_monitor($media_id) {
		$media_id = $this->db->escape_str ( $media_id );
		
		$sql = "select
						monitor_id,
						industry.industry_name as industry_name,
						industry_category.industry_name as industry_category_name,
						product_name,
						monitor_time,
						brand_name 
					from 	monitor
						inner join sys_industry as industry  on monitor.industry =industry.industry_id
						inner join sys_industry as industry_category  on monitor.industry_category =industry_category.industry_id
						inner join sys_brand on sys_brand.brand_id =monitor.brand 
					where media_id ='$media_id'	 
					order by monitor_id desc 
				";
		
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function query_vendor_info($vendor_id) {
		$vendor_id = $this->db->escape_str ( $vendor_id );
		$sql = "select 	company_name,
						address,
						contactsname0,
						contactspost0,
						contactsphone0
				from ven_vendor
				where vendor_id ='$vendor_id' limit 1
				";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->row_array ();
		}
	}
	public function query_nearby_media($longitude, $latitude) {
		$longitude = floatval ( $longitude );
		$latitude = floatval ( $latitude );
		$sql = "select 	ABS(longitude -'$longitude') as lo_number,
						ABS(latitude -'$latitude') as la_number,
						media.media_id,
						pic,
						location   
				from media 
					inner join media_pic on media.media_id =media_pic.media_id 
				group by media_id
 				order by (lo_number+la_number) asc,pic_id asc 
				limit 1,5";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function query_media_env($media_id) {
		$media_id = $this->db->escape_str ( $media_id );
		$sql = "select env_id,env_type from media_env where media_id ='$media_id' limit 1";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->row_array ();
		}
	}
	public function query_env_urban_env($env_id, $env_type) {
		$sql = "select * from env_urban_$env_type where env_id ='$env_id' limit 1";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->row_array ();
		}
	}
	public function query_highway_info($highway_id) {
		$sql = "select * from env_highway where highway_id ='$highway_id' limit 1";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->row_array ();
		}
	}
	public function query_airport_info($airport_id) {
		$sql = "select * from env_airport where airport_id ='$airport_id' limit 1 ";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->row_array ();
		}
	}
}